﻿using Fdb.Model;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Reflection;
using System.Text;

namespace Fdb.Mysql
{
    /// <summary>
    /// Mysql数据库操作类(数据层)
    /// </summary>
    internal class Manager<T> : DBHandler<T>
    {
        #region 连接字符串

        private MysqlHelper _MysqlHelper = new MysqlHelper();
        public Manager(string connectionString, DatabaseType databaseType) : base(databaseType)
        {
            _MysqlHelper.ConnectionString = connectionString;
        }

        #endregion 连接字符串

        /// <summary>
        /// 创建数据库
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="dbname"></param>
        public override ExecuteResult CreateDB(string connectionString, string dbname)
        {
            ExecuteResult er = new ExecuteResult();
            MySqlConnection conn = new MySqlConnection(connectionString);
            MySqlCommand cmd = new MySqlCommand("CREATE DATABASE " + dbname + ";", conn);

            conn.Open();

            try
            {
                cmd.ExecuteNonQuery();
                conn.Close();
                er.Success = true;
            }
            catch (Exception ex)
            {
                conn.Close();
                er.Success = false;
                er.ResultString = "建立数据库失败!" + ex.ToString();
                //throw;
            }

            return er;
        }

        #region 获取数据库结构抽象方法

        /// <summary>
        /// 获取指定数据库下所有表信息
        /// </summary>
        /// <param name="dbname">数据库名称</param>
        /// <returns></returns>
        public override List<TableClass> GetDbTableList(string dbname)
        {
            List<TableClass> tablelist = new List<TableClass>();
            string sql = "select table_name,table_comment from information_schema.tables where table_schema='" + dbname + "'";
            DataTable dt = this.GetDataTable(sql);
            foreach (DataRow dr in dt.Rows)
            {
                TableClass tbl = new TableClass();
                tbl.TableName = dr["table_name"].ToString();
                tbl.TableCNName = dr["table_comment"].ToString();
                tablelist.Add(tbl);
            }

            return tablelist;
        }

        /// <summary>
        /// 获取指定数据库下指定表的所有字段信息
        /// </summary>
        /// <param name="dbname">数据库名称</param>
        /// <param name="tblname">表名称</param>
        /// <returns></returns>
        public override List<FieldClass> GetTblFieldList(string dbname, string tblname)
        {
            List<FieldClass> fieldList = new List<FieldClass>();
            string sql = "select COLUMN_NAME,IS_NULLABLE,COLUMN_TYPE,COLUMN_KEY,COLUMN_COMMENT from information_schema.COLUMNS where table_name = '" + tblname + "' and table_schema = '" + dbname + "'";
            DataTable dt = this.GetDataTable(sql);
            foreach (DataRow dr in dt.Rows)
            {
                FieldClass field = new FieldClass();
                field.FieldName = dr["COLUMN_NAME"].ToString();
                field.FieldCNName = dr["COLUMN_COMMENT"].ToString();
                field.Nullable = dr["IS_NULLABLE"].ToString().ToUpper().IndexOf("YES") > -1 ? true : false;

                string column_type = dr["COLUMN_TYPE"].ToString().ToLower();
                if (column_type.IndexOf("int") > -1 && column_type.IndexOf("big") < 0)
                {
                    field.FieldTypeName = "int"; field.FieldTypeName += "?";
                }
                else if (column_type.IndexOf("bigint") > -1)
                {
                    field.FieldTypeName = "long"; field.FieldTypeName += "?";
                }
                else if (column_type.IndexOf("decimal") > -1)
                {
                    field.FieldTypeName = "double"; field.FieldTypeName += "?";
                }
                else if (column_type.IndexOf("varchar") > -1)
                {
                    field.FieldTypeName = "string";
                }
                else if (column_type.IndexOf("longtext") > -1)
                {
                    field.FieldTypeName = "string";
                }
                else if (column_type.IndexOf("date") > -1)
                {
                    field.FieldTypeName = "DateTime"; if (field.Nullable) field.FieldTypeName += "?";
                }

                field.IsPrimaryKey = dr["COLUMN_KEY"].ToString().ToUpper().IndexOf("PRI") > -1 ? true : false;


                if (field.IsPrimaryKey)
                {
                    field.FieldTypeName = "long";
                }

                fieldList.Add(field);
            }

            return fieldList;
        }

        #endregion 获取数据库结构抽象方法

        #region 表维护抽象方法

        /// <summary>
        /// 创建一个默认数据表
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <returns></returns>
        public override ExecuteResult CreateTable(string tableName)
        {
            string sql = "Create TABLE " + tableName + "(";
            sql += "ID INT not null auto_increment primary key,";
            sql += "ADDUSERID bigint(20) DEFAULT NULL,";
            sql += "ADDTIME datetime DEFAULT NULL,";
            sql += "EDITUSERID bigint(20) DEFAULT NULL,";
            sql += "EDITTIME datetime DEFAULT NULL,";
            sql += "GUID varchar(200) DEFAULT NULL,";
            sql += "VER INT DEFAULT NULL";
            sql += ")";
            return this.ExecuteSQLWithResult(sql);
        }

        /// <summary>
        /// 根据实体类创建表
        /// </summary>
        /// <returns></returns>
        public override ExecuteResult CreateTable()
        {
            string sql = "Create TABLE " + this._TableName + "(";
            string fields = string.Empty;
            PropertyInfo[] props = GetPropertyInfoArray();
            foreach (PropertyInfo proper in props)
            {
                string fType = string.Empty;
                string type = proper.PropertyType.ToString();
                switch (type)
                {
                    case "System.Int64": fType = " BIGINT"; break;
                    case "System.Int32": fType = " INT"; break;
                    case "System.String": fType = " VARCHAR(200)"; break;
                    case "System.DateTime": fType = " DATE"; break;
                    case "System.Double": fType = " DECIMAL"; break;
                    case "System.Nullable`1[System.Int32]": fType = " INT"; break;
                    case "System.Nullable`1[System.Int64]": fType = " BIGINT"; break;
                    case "System.Nullable`1[System.DateTime]": fType = " DATE"; break;
                    case "System.Nullable`1[System.Double]": fType = " DECIMAL"; break;
                }

                string fieldstr = proper.Name + fType;

                //var userKeyFields = ReflectionHelper.getDbKeyFields<T>();
                ////var key = userKeyFields.Select(field => field.Name==proper.Name);
                ////if (key.Count() > 0)
                ////{
                ////    fieldstr += " not null primary key";
                ////}

                //foreach (PropertyInfo pinfo in userKeyFields)
                //{
                //    if (pinfo.Name.Equals(proper.Name))
                //    {
                //        fieldstr += " not null auto_increment primary key";
                //        break;
                //    }
                //}

                if (this.IsPrimaryKey(proper.Name)) fieldstr += " not null auto_increment primary key";
                if (!string.IsNullOrEmpty(fields)) fieldstr = "," + fieldstr;

                fields += fieldstr;

            }

            sql += fields;
            sql += ")";

            ExecuteResult er = this.ExecuteSQLWithResult(sql);

            return er;
        }

        /// <summary>
        /// 修改表名
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <returns></returns>
        public override ExecuteResult ModifyTable(string oldTableName, string newTableName)
        {
            string sql = "ALTER  TABLE " + oldTableName + " RENAME TO " + newTableName;
            return this.ExecuteSQLWithResult(sql);
        }

        /// <summary>
        /// 判断数据表是否存在
        /// </summary>
        /// <param name="field"></param>
        /// <returns></returns>
        public override bool IsFieldExist(FieldClass field)
        {
            string sql = "select count(1) from information_schema.columns where table_name = @name AND column_name = @fieldname";
            NHashtable nht = new NHashtable();
            nht.Add("name", field.TableName);
            nht.Add("fieldname", field.FieldName);
            object countObj = this.GetDataObject(sql, nht);
            int count = 0;
            if (countObj != null && !string.IsNullOrEmpty(countObj.ToString()))
            {
                count = Convert.ToInt32(countObj.ToString());
                if (count > 0)
                {
                    return true;
                }
            }

            return false;
        }

        /// <summary>
        /// 根据字段类型编码获取字段类型文本
        /// </summary>
        /// <param name="fieldTypeCode"></param>
        /// <returns></returns>
        public string GetFieldTypeName(int fieldTypeCode, int length, int deci)
        {
            switch (fieldTypeCode)
            {
                case 1: return " INT";
                case 2: return " DECIMAL(10," + deci + ")";
                case 3: return " VARCHAR(" + length + ")";
                case 4: return " DATETIME";
                case 5: return " LONGTEXT";
            }

            return " VARCHAR(200)";
        }

        /// <summary>
        /// 新增字段
        /// </summary>
        /// <param name="field">字段</param>
        /// <returns></returns>
        public override ExecuteResult CreateField(FieldClass field)
        {
            string sql = "ALTER TABLE " + field.TableName + " ADD " + field.FieldName + " " + GetFieldTypeName(field.FieldType, field.FieldLength, field.FieldScale);
            return this.ExecuteSQLWithResult(sql);
        }

        /// <summary>
        /// 删除字段
        /// </summary>
        /// <param name="field">field</param>
        /// <returns></returns>
        public override ExecuteResult DeleteField(FieldClass field)
        {
            ExecuteResult er = new ExecuteResult();
            string sql = "select count(*) from information_schema.columns where table_name = '" + field.TableName + "' and column_name = '" + field.FieldName + "'";
            object numobj = this.GetDataObject(sql);
            if (numobj != null && !string.IsNullOrEmpty(numobj.ToString()))
            {
                int _num = Convert.ToInt32(numobj);
                if (_num > 0)
                {
                    sql = "ALTER TABLE " + field.TableName + " DROP COLUMN " + field.FieldName;
                    er = this.ExecuteSQLWithResult(sql);
                    return er;
                }
            }

            er.Success = true;
            return er;
        }

        /// <summary>
        /// 修改表名
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <returns></returns>
        public override ExecuteResult ModifyField(FieldClass oldField, FieldClass newField)
        {
            string sql = "ALTER TABLE " + oldField.TableName + " CHANGE " + oldField.FieldName + " " + newField.FieldName + " " + GetFieldTypeName(newField.FieldType, newField.FieldLength, newField.FieldScale);
            return this.ExecuteSQLWithResult(sql);
        }

        /// <summary>
        ///  创建业务流程视图
        /// </summary>
        /// <param name="bizViewName"></param>
        /// <param name="primary"></param>
        /// <param name="datasouceSQL"></param>
        /// <param name="flowCode"></param>
        /// <returns></returns>
        public override ExecuteResult CreateBizFlowView(string flowCode, string bizViewName, string primary, string datasouceSQL)
        {
            ExecuteResult result = new ExecuteResult();
            string str = string.Empty;
            string str2 = "CREATE OR REPLACE VIEW " + bizViewName + " AS select t.*,IF(o.FlowCode IS NULL,'" + flowCode + "',o.FlowCode) as FlowCode,IF(o.BizRecordGuid IS NULL,t." + primary + ",o.BizRecordGuid) as BizRecordGuid,IF(o.BizRecordId IS NULL,t.ID,o.BizRecordId) as BizRecordId,IF(o.BizRecordState IS NULL,1,o.BizRecordState) as BizRecordState,IF(o.BizRecordState_Text IS NULL,'<span style=\"color:green\">未申报</span>',o.BizRecordState_Text) as BizRecordState_Text,IF(o.CurrentNodeLevel IS NULL,0,o.CurrentNodeLevel) as CurrentNodeLevel,o.ActorId,o.ActorName from ( " + datasouceSQL + ") t ";
            str = str2 + "left join (select * from WF_Current where FlowCode like '" + flowCode + "') o on t." + primary + "=o.BizRecordGuid";
            return this.ExecuteSQLWithResult(str);
        }

        #endregion 表维护抽象方法

        #region 基础方法

        /// <summary>
        /// 测试连接
        /// </summary>
        /// <returns></returns>
        public override ExecuteResult TestConnnection()
        {
            return null;
        }

        /// <summary>
        /// 获得DataTable对象
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public override DataTable GetDataTable(string sql)
        {
            DataSet dataSet = new DataSet();

            try
            {
                dataSet = _MysqlHelper.GetDataSet(CommandType.Text, sql, null);
            }

            catch (Exception ex)
            {
                throw ex;
            }

            return dataSet.Tables[0];
        }

        /// <summary>
        /// 获得DataTable对象
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="hasParam"></param>
        /// <returns></returns>
        public override DataTable GetDataTable(string sql, NHashtable ht)
        {
            DataSet dataSet = new DataSet();
            MySqlParameter[] parlist = this.GetDbParameterArray(ht);

            try
            {
                dataSet = _MysqlHelper.GetDataSet(CommandType.Text, sql, parlist);
            }

            catch (Exception ex)
            {
                throw ex;
            }

            return dataSet.Tables[0];
        }

        /// <summary>
        /// 根据存储过程获取DataTable对象
        /// </summary>
        /// <param name="storeProcName">存储过程名称</param>
        /// <returns></returns>
        public override DataTable GetDataTableWithStoreProc(string storeProcName)
        {
            return GetDataTableWithStoreProc(storeProcName, null);
        }

        /// <summary>
        /// 根据存储过程获取DataTable对象
        /// </summary>
        /// <param name="storeProcName">存储过程名称</param>
        /// <param name="ht">参数</param>
        /// <returns></returns>
        public override DataTable GetDataTableWithStoreProc(string storeProcName, NHashtable ht)
        {
            DataSet dataSet = new DataSet();
            MySqlParameter[] parlist = this.GetDbParameterArray(ht);

            try
            {
                dataSet = _MysqlHelper.GetDataSet(CommandType.StoredProcedure, storeProcName, parlist);
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return dataSet.Tables[0];
        }

        /// <summary>
        /// 执行sql
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public override ExecuteResult ExecuteSQLWithResult(string sql)
        {
            ExecuteResult er = new ExecuteResult();
            er.Success = true;
            er.Sql = sql;

            try
            {
                _MysqlHelper.ExecuteNonQuery(CommandType.Text, sql, null);
            }

            catch (Exception ex)
            {
                er.Success = false;
                er.ResultString = ex.ToString();
            }

            return er;
        }

        /// <summary>
        /// 执行sql
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        public override ExecuteResult ExecuteSQLWithConn(string conn, string sql)
        {
            ExecuteResult er = new ExecuteResult();
            er.Success = true;
            er.Sql = sql;

            try
            {
                _MysqlHelper.ConnectionString = conn;
                _MysqlHelper.ExecuteNonQuery(CommandType.Text, sql, null);
            }

            catch (Exception ex)
            {
                er.Success = false;
                er.ResultString = ex.ToString();
            }

            return er;
        }

        /// <summary>
        /// 执行带参数的sql
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public override ExecuteResult ExecuteSQLWithResult(string sql, NHashtable ht)
        {
            ExecuteResult er = new ExecuteResult();
            er.Success = true;
            er.Sql = sql;

            try
            {
                MySqlParameter[] parlist = this.GetDbParameterArray(ht);
                long id = _MysqlHelper.ExecuteNonQuery(CommandType.Text, sql, parlist);
                if (id > 0)
                {
                    er.ID = (int)id;
                }
            }

            catch (Exception ex)
            {
                er.Success = false;
                er.ResultString = ex.ToString();
            }

            return er;
        }

        /// <summary>
        /// 执行事务
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="ht"></param>
        /// <returns></returns>
        public override ExecuteResult ExcuteTransactionSQL(string sql, NHashtable ht)
        {
            ExecuteResult er = new ExecuteResult();
            er.Success = true;
            er.Sql = sql;

            try
            {
                MySqlParameter[] parlist = this.GetDbParameterArray(ht);
                long id = _MysqlHelper.ExcuteTransactionSQL(sql, parlist);
                if (id > 0)
                {
                    er.ID = (int)id;
                }
            }

            catch (Exception ex)
            {
                er.Success = false;
                er.ResultString = ex.ToString();
            }

            return er;
        }

        /// <summary>
        /// 获得单个object变量
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public override object GetDataObject(string sql)
        {
            object o = null;

            try
            {
                o = this.GetDataObject(sql, null);
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return o;

        }

        /// <summary>
        /// 获得单个object变量
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="ht"></param>
        /// <returns></returns>
        public override object GetDataObject(string sql, NHashtable ht)
        {
            MySqlParameter[] parlist = this.GetDbParameterArray(ht);

            try
            {
                object obj = _MysqlHelper.ExecuteScalar(CommandType.Text, sql, parlist);
                return obj;

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 获取分页查询结果
        /// </summary>
        /// <returns></returns>
        public override DataTable GetDataTable(PagedQuery pagedQuery)
        {
            if (!string.IsNullOrEmpty(pagedQuery.TableName)) this._TableName = pagedQuery.TableName;

            string sql = string.Empty;
            string where = string.Empty;
            string sortClause = string.Empty;

            if (!string.IsNullOrEmpty(pagedQuery.TMPWhereClause)) where = " where " + pagedQuery.TMPWhereClause;
            if (!string.IsNullOrEmpty(pagedQuery.SortClause)) sortClause = pagedQuery.SortClause;
            else sortClause = pagedQuery.IdField;

            string joinstr = string.IsNullOrEmpty(pagedQuery.JoinStr) ? string.Empty : (pagedQuery.JoinStr.Trim() + " ");
            sql = string.Format("select " + pagedQuery.FieldIDS + " from " + this._TableName + " t " + joinstr + where + " order by " + sortClause + " limit {0},{1}", pagedQuery.PageSize * (pagedQuery.PageIndex - 1), pagedQuery.PageSize);

            DataTable dt = new DataTable();

            try
            {
                dt = this.GetDataTable(sql, pagedQuery.TMPParamList);
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return dt;
        }

        /// <summary>
        /// 获取记录总数
        /// </summary>
        /// <returns></returns>
        public override int GetCount(PagedQuery pagedQuery)
        {
            if (!string.IsNullOrEmpty(pagedQuery.TableName)) this._TableName = pagedQuery.TableName;
            string joinstr = string.IsNullOrEmpty(pagedQuery.JoinStr) ? string.Empty : (pagedQuery.JoinStr.Trim() + " ");
            string sql = "select Count(*) from " + this._TableName + " t " + joinstr + (string.IsNullOrEmpty(pagedQuery.TMPWhereClause) ? string.Empty : (" where " + pagedQuery.TMPWhereClause));
            object o = null;

            try
            {
                o = this.GetDataObject(sql, pagedQuery.TMPParamList);
                return int.Parse(o.ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 执行带参数的存储过程
        /// </summary>
        /// <param name="storedProcName"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public override int RunProcedure(string storedProcName, IDataParameter[] parameters)
        {
            object o = null;

            try
            {
                o = this._MysqlHelper.ExecuteProc(storedProcName, parameters);
                return int.Parse(o.ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion 基础方法

        #region 增删改方法

        /// <summary>
        /// 添加数据
        /// </summary>
        /// <param name="tablename">表名</param>
        /// <param name="ht">Hashtable</param>
        /// <param name="fieldTypes">所有字段类型值</param>
        /// <param name="fieldSizes">所有字段长度值</param>
        /// <returns></returns>
        public override ExecuteResult Insert(string tableName, NHashtable ht)
        {
            string sql = "insert into " + tableName + "(";
            string realSql = string.Empty;

            int i = 0;
            foreach (string str in ht.Keys)
            {
                sql += str;
                if (i < ht.Count - 1) sql += ",";
                i++;
            }

            sql += ")";
            sql += " values(";
            realSql = sql;

            i = 0;
            string realVal = string.Empty;//真实值
            foreach (string str in ht.Keys)
            {
                sql += "@" + str;

                realVal = Common.GetRealVal(ht[str]);
                realSql += realVal;

                if (i < ht.Count - 1)
                {
                    sql += ",";
                    realSql += ",";
                }

                i++;
            }

            sql += ")";
            realSql += ")";

            ExecuteResult er = new ExecuteResult();

            try
            {
                er.Success = true;
                er = this.ExecuteSQLWithResult(sql, ht);
                er.IsAdd = true;
                er.Sql = realSql;
            }
            catch (Exception ex)
            {
                er.Success = false;
                er.ResultString = ex.ToString();
            }

            return er;
        }

        /// <summary>
        /// 添加数据
        /// </summary>
        /// <param name="t"></param>
        /// <returns></returns>
        public override ExecuteResult Insert(T t)
        {
            NHashtable nht = new NHashtable();
            nht = CreateContentValues(t);

            PropertyInfo key = Common.GetFirstDbKeyField<T>();
            if (key != null)
            {
                bool is_auto_identity = Common.IsUseWithDescription(key);
                if (is_auto_identity)
                {
                    nht.Remove(key.Name);//删除主键
                }
            }

            return this.Insert(this._TableName, nht);
        }

        /// <summary>
        /// 编辑数据
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="ht">Hashtable</param>
        /// <param name="condition">条件</param>
        /// <param name="fieldTypes">所有字段类型</param>
        /// <param name="fieldSizes">所有字段大小</param>
        /// <param name="realWhere">实际where语句</param>
        /// <returns></returns>
        public ExecuteResult Update(string tableName, NHashtable ht, string where, string realWhere)
        {
            string sql = "update " + tableName + " set ";
            string realSql = sql;

            int i = 0;
            foreach (string str in ht.Keys)
            {
                if (i == ht.Count - 1) break;//HACK：此处有待优化。
                sql += str + "=@" + str;
                realSql += str + "=" + Common.GetRealVal(ht[str]);

                if (i < ht.Count - 2)
                {
                    sql += ",";
                    realSql += ",";
                }

                i++;
            }

            sql += " " + where;

            if (string.IsNullOrEmpty(realWhere))
            {
                realSql = sql;
            }
            else
            {
                realSql += " " + realWhere;
            }

            ExecuteResult er = new ExecuteResult();

            try
            {
                er = this.ExecuteSQLWithResult(sql, ht);
                er.IsAdd = false;
                er.Sql = realSql;
            }
            catch (Exception ex)
            {
                er.Success = false;
                er.ResultString = ex.ToString();
            }

            return er;
        }

        /// <summary>
        /// 编辑数据
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="ht">Hashtable</param>
        /// <param name="condition">条件</param>
        /// <param name="fieldTypes">所有字段类型</param>
        /// <param name="fieldSizes">所有字段大小</param>
        /// <returns></returns>
        public override ExecuteResult Update(string tableName, NHashtable ht, string where)
        {
            return Update(tableName, ht, where, string.Empty);
        }

        /// <summary>
        /// 编辑数据
        /// </summary>
        /// <param name="t"></param>
        /// <returns></returns>
        public override ExecuteResult Update(T t)
        {
            NHashtable nht = new NHashtable();
            nht = CreateContentValues(t);
            object keyValue = null;
            PropertyInfo key = Common.GetFirstDbKeyField<T>();
            if (key != null)
            {
                nht.Remove(key.Name);                      //删除主键
                keyValue = key.GetValue(t, null);
            }
            else
            {
                return new ExecuteResult { ResultString = "未找到主键！", Success = false };
            }

            nht.Add(key.Name, keyValue);
            ExecuteResult er = this.Update(this._TableName, nht, "Where " + key.Name + " = @" + key.Name, "Where " + key.Name + " = " + Common.GetRealVal(keyValue));
            er.ID = Common.ConvertToIntWithOutNull(keyValue);
            return er;
        }

        /// <summary>
        /// 根据编号删除数据
        /// </summary>
        /// <param name="t"></param>
        /// <returns></returns>
        public override ExecuteResult Delete(long id)
        {
            string sql = "Delete from " + this._TableName + " Where ID = " + id;
            return this.ExecuteSQLWithResult(sql, null);
        }

        /// <summary>
        /// 根据GUID删除数据
        /// </summary>
        /// <param name="t"></param>
        /// <returns></returns>
        public override ExecuteResult Delete(Guid guid)
        {
            string sql = "Delete from " + this._TableName + " Where guid = '" + guid + "'";
            return this.ExecuteSQLWithResult(sql, null);
        }

        /// <summary>
        /// 删除指定对象
        /// </summary>
        /// <param name="t"></param>
        /// <returns></returns>
        public override ExecuteResult Delete(T t)
        {
            string sql = "Delete from " + this._TableName;
            string where = this.BuildWhere(t);
            NHashtable paramtable = this.BuildParams(t);
            if (!string.IsNullOrEmpty(where)) where = " where " + where;
            sql += where;
            return this.ExecuteSQLWithResult(sql, paramtable);
        }

        #endregion 增删改方法

        #region 参数构造方法

        /// <summary>
        /// 获得数据库参数数组
        /// </summary>
        /// <param name="ht"></param>
        /// <returns></returns>
        private MySqlParameter[] GetDbParameterArray(NHashtable ht)
        {
            MySqlParameter[] parlist = null;
            if (ht != null && ht.Count > 0)
            {
                parlist = new MySqlParameter[ht.Count];
                int i = 0;

                foreach (string str in ht.Keys)
                {
                    MySqlDbType dbType = Common.GetMysqlDbType(ht[str]);
                    object value = ht[str] == null ? DBNull.Value : ht[str];
                    MySqlParameter myParameter = new MySqlParameter("@" + str, dbType);
                    myParameter.Value = value;
                    parlist[i] = myParameter;
                    i++;
                }
            }

            return parlist;
        }

        #endregion 参数构造方法

        #region 泛型查询方法

        /// <summary>
        /// 根据编号获取单个实例
        /// </summary>
        /// <param name="t"></param>
        /// <returns></returns>
        public override T Fetch(long id)
        {
            PropertyInfo key = Common.GetFirstDbKeyField<T>();
            return Fetch("select * from " + this._TableName + " where " + key.Name + "=" + id);
        }

        /// <summary>
        /// 根据GUID获取单个实例
        /// </summary>
        /// <param name="t"></param>
        /// <returns></returns>
        public override T Fetch(Guid guid)
        {
            return Fetch("select * from " + this._TableName + " where guid='" + guid.ToString() + "'");
        }

        /// <summary>
        /// 根据sql获取实例对象
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public override T Fetch(string sql)
        {
            return Fetch(sql, null);
        }

        /// <summary>
        /// 根据sql和查询条件获取实例
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="nht"></param>
        /// <returns></returns>
        public override T Fetch(string sql, NHashtable nht)
        {
            DataRow dr = this.GetDataRow(sql, nht);
            return Fetch(dr);
        }

        /// <summary>
        /// 根据实体对象获取列表
        /// </summary>
        /// <returns></returns>
        public override T Fetch(T t)
        {
            List<T> list = FetchList(t);
            if (list.Count > 0) return list[0];

            return default(T);
        }

        /// <summary>
        /// 获取全部实例对象列表
        /// </summary>
        /// <returns></returns>
        public override List<T> FetchList()
        {
            string sql = "select * from " + this._TableName;
            return FetchList(sql, null);
        }

        /// <summary>
        /// 根据sql获取实例对象列表
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public override List<T> FetchList(string sql)
        {
            return FetchList(sql, null);
        }

        /// <summary>
        /// 根据sql和参数表获取实例对象列表
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="ht"></param>
        /// <returns></returns>
        public override List<T> FetchList(string sql, NHashtable ht)
        {
            List<T> list = new List<T>();
            DataTable dt = this.GetDataTable(sql, ht);
            if (dt == null) return list;

            foreach (DataRow dr in dt.Rows)
            {
                T instance = Fetch(dr);
                list.Add(instance);
            }

            return list;
        }

        /// <summary>
        /// 根据实体对象获取列表
        /// </summary>
        /// <returns></returns>
        public override List<T> FetchList(T t, string whereClause)
        {
            string sql = "select * from " + this._TableName;
            string where = this.BuildWhere(t);
            NHashtable paramtable = this.BuildParams(t);
            if (!string.IsNullOrEmpty(where))
            {
                where = " where " + where;
            }

            if (!string.IsNullOrEmpty(whereClause))
            {
                if (string.IsNullOrEmpty(where))
                {
                    where = " where ";
                }
                else
                {
                    where += " and ";
                }
            }

            where += whereClause;
            sql += where;
            return FetchList(sql, paramtable);
        }

        /// <summary>
        /// 根据实体对象获取列表
        /// </summary>
        /// <returns></returns>
        public override List<T> FetchList(T t)
        {
            string sql = "select * from " + this._TableName;
            string where = this.BuildWhere(t);
            NHashtable paramtable = this.BuildParams(t);
            if (!string.IsNullOrEmpty(where)) where = " where " + where;
            sql += where;
            return FetchList(sql, paramtable);
        }

        /// <summary>
        /// 将DataRow数组转为List
        /// </summary>
        /// <param name="drs"></param>
        /// <returns></returns>
        public override List<T> FetchList(DataRow[] drs)
        {
            List<T> list = new List<T>();
            foreach (DataRow dr in drs)
            {
                T instance = Fetch(dr);
                list.Add(instance);
            }

            return list;
        }

        /// <summary>
        /// 将DataTable类型转为List
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        private List<T> FetchList(DataTable dt)
        {
            List<T> list = new List<T>();
            if (dt == null) return list;

            foreach (DataRow dr in dt.Rows)
            {
                T instance = Fetch(dr);
                list.Add(instance);
            }

            return list;
        }

        /// <summary>
        /// 获取分页结果
        /// </summary>
        /// <param name="t"></param>
        /// <param name="pagedQuery"></param>
        /// <returns></returns>
        public override List<T> FetchPageList(T t, PagedQuery pagedQuery)
        {
            this.InitPagedQuery(t, pagedQuery);
            DataTable dt = GetDataTable(pagedQuery);
            return FetchList(dt);
        }

        /// <summary>
        /// 获取记录总数
        /// </summary>
        /// <param name="t"></param>
        /// <param name="pagedQuery"></param>
        /// <returns></returns>
        public override int FetchCount(T t, PagedQuery pagedQuery)
        {
            this.InitPagedQuery(t, pagedQuery);
            int count = GetCount(pagedQuery);
            return count;
        }

        /// <summary>
        /// 获取记录总数
        /// </summary>
        /// <param name="t"></param>
        /// <returns></returns>
        public override int FetchCount(T t)
        {
            string sql = "select Count(*) from " + this._TableName;
            string where = this.BuildWhere(t);
            NHashtable paramtable = this.BuildParams(t);
            if (!string.IsNullOrEmpty(where)) where = " where " + where;
            sql += where;

            object obj = this.GetDataObject(sql, paramtable);
            int count = Common.ConvertToIntWithMinValue(obj);
            return count;
        }

        #endregion 泛型查询抽象方法
    }
}
